﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using BrokerBase.Lib.DataBase;
using NHibernate;
using System.Data;
using BrokerBase.Lib.Tool;
using System.Xml;
using System.IO;

namespace Broker.Update
{
    public class DataUpdateManager
    {
        public IList<DBTable> LoadClientDBTableInfo(TableType tt, ISession sess)
        {
            try
            {
                IList<DBTable> tables = sess.CreateQuery("from DBTable where TableType=:tt and IsNeedUpdate=True").SetInt32("tt", (int)tt).List<DBTable>();

                foreach (var t in tables)
                {
                    DBTableData ta = new DBTableData();
                    ta.ClientTs = t.LastTimeStamp;
                    ta.Name = t.Name;

                    string sql = "select count(*)as c,min(id) as minId,max(id) as maxId from " + t.Name;

                    //IList<object[]> ss = sess.CreateSQLQuery(sql).AddScalar("c", NHibernateUtil.Int64).AddScalar("minId", NHibernateUtil.Int64).AddScalar("maxId", NHibernateUtil.Int64).List<object[]>();
                    object[] r = sess.CreateSQLQuery(sql).AddScalar("c", NHibernateUtil.Int64).AddScalar("minId", NHibernateUtil.Int64).AddScalar("maxId", NHibernateUtil.Int64).List<object[]>()[0];

                    ta.ClientRowCount = (long)r[0];
                    ta.ClientMinId = r[1] == null ? 0 : (long)r[1];
                    ta.ClientMaxId = r[2] == null ? 0 : (long)r[2];

                    t.TableData = ta;
                }

                return tables;
            }
            catch (Exception er)
            {
                throw er;
            }

        }
        /// <summary>
        /// 更新客户端表数据
        /// </summary>
        /// <param name="table"></param>
        /// <param name="sess"></param>
        public void UpdateTable(DBTable table, IDbConnection conn, IDbTransaction tx, Action<string, bool> callBack)
        {
            DBTableData tableData = table.TableData;
            try
            {
                if (tableData.Data != null)
                {
                    DataSet ds = BuildDataSet(tableData.Data, table.Name);

                    IDbCommand cmd = BuildCmdText(ds, table.Name, conn);
                    cmd.Transaction = tx;

                    int rows = ds.Tables[0].Rows.Count;

                    if (rows > 0)
                    {
                        if(callBack!=null)
                            callBack("更新" + rows + "条数据......", false);
                        if (rows > 100)
                        {
                            if(callBack!=null)
                                callBack("数据比较多，请等待......", false); 
                        }
                    }
                    UpdateOrInsertTable(ds, table, cmd, tx);

                    if (tableData.IsExistDeletedRows)//存在需要删除的行
                    {
                        DeleteRows(table, conn);
                    }
                }
            }
            catch (Exception er)
            {
                throw er;
            }
        }

        private DataSet BuildDataSet(byte[] data, string tableName)
        {
            string s = TransportUtil.DecompressText(data);
            StringReader sr = new StringReader(s);

            XmlTextReader reader = new XmlTextReader(sr);
            DataSet ds = new DataSet(tableName);

            ds.ReadXml(reader, XmlReadMode.ReadSchema);

            return ds;
        }

        private IDbCommand BuildCmdText(DataSet ds, string tableName, IDbConnection conn)
        {

            IDbCommand cmd = conn.CreateCommand();

            StringBuilder insertSb = new StringBuilder();
            StringBuilder updateSb = new StringBuilder();
            StringBuilder insertValuesSb = new StringBuilder();

            string s = "if exists (select 1 from " + tableName + " where id=@Id_B)";

            IDbDataParameter p0 = cmd.CreateParameter();
            p0.DbType = DbType.Int32;
            p0.ParameterName = "@Id_B";

            cmd.Parameters.Add(p0);

            updateSb.Append("   update " + tableName + " set ");
            insertSb.Append("   insert or replace into " + tableName + " (");//sqllite 语法,sql 
            insertValuesSb.Append(" values( ");

            DataColumn col = null;

            int updateCols = 0;

            for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
            {
                col = ds.Tables[0].Columns[i];

                if (col.ColumnName.ToUpper() != "TS")
                {
                    if (col.ColumnName.ToUpper() != "ID")
                    {
                        updateSb.Append((updateCols == 0 ? " " : " ,") + col.ColumnName + " = @" + col.ColumnName + "_U");

                        IDbDataParameter p1 = cmd.CreateParameter();
                        p1.ParameterName = "@" + col.ColumnName + "_U";

                        p1.Size = col.MaxLength;
                        cmd.Parameters.Add(p1);
                        updateCols++;
                    }
                    insertSb.Append((i == 0 ? "" : " ,") + col.ColumnName);
                    insertValuesSb.Append((i == 0 ? "" : " ,") + "@" + col.ColumnName + "_I");

                    IDbDataParameter p2 = cmd.CreateParameter();
                    p2.ParameterName = "@" + col.ColumnName + "_I";

                    p2.Size = col.MaxLength;
                    cmd.Parameters.Add(p2);
                }
            }
            updateSb.Append(" where id=@Id_E");
            IDbDataParameter p3 = cmd.CreateParameter();
            p3.ParameterName = "@Id_E";
            p3.DbType = DbType.Int32;
            cmd.Parameters.Add(p3);

            insertSb.Append(" )");
            insertValuesSb.Append(")");

            StringBuilder sqlSb = new StringBuilder();
            sqlSb.Append(s);
            sqlSb.Append(Environment.NewLine);
            sqlSb.Append("    ");
            sqlSb.Append(updateSb.ToString());
            sqlSb.Append(Environment.NewLine);
            sqlSb.Append("else");
            sqlSb.Append(Environment.NewLine);
            sqlSb.Append("    ");
            sqlSb.Append(insertSb.ToString());
            sqlSb.Append(insertValuesSb.ToString());

            StringBuilder sqlSb2 = new StringBuilder();

            sqlSb2.Append(insertSb.ToString());
            sqlSb2.Append(insertValuesSb.ToString());

            string sql = sqlSb2.ToString();
            cmd.CommandText = sql;
            cmd.Connection = conn;
            return cmd;
        }
        /// <summary>
        /// 更新或修改行数据
        /// </summary>
        /// <param name="ds"></param>
        /// <param name="table"></param>
        /// <param name="cmd"></param>
        /// <param name="tx"></param>
        private void UpdateOrInsertTable(DataSet ds, DBTable table, IDbCommand cmd, IDbTransaction tx)
        {
            IDbConnection cnn = cmd.Connection;

            foreach (DataRow dr in ds.Tables[0].Rows)
            {

                foreach (IDbDataParameter par in cmd.Parameters)
                {
                    string colName = par.ParameterName.Substring(1, par.ParameterName.Length - 3);

                    par.Value = dr[colName];

                }

                cmd.ExecuteNonQuery();
            }

            IDbCommand cmdTimeStamp = cnn.CreateCommand();
            cmdTimeStamp.CommandText = "update System_TableUpdate set LastTimeStamp=" + table.TableData.ServerTs + " where Name='" + table.Name + "'";

            cmdTimeStamp.Transaction = tx;
            cmdTimeStamp.ExecuteNonQuery();

        }
        /// <summary>
        /// 删除行数据
        /// </summary>
        /// <param name="table"></param>
        /// <param name="sess"></param>
        /// <returns></returns>
        public int DeleteRows(DBTable table, IDbConnection cnn)
        {
            try
            {
                string sql = "create TEMPORARY table tmp_" + table.Name + "(id)";


                IDbCommand cmd = cnn.CreateCommand();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                int[] ids = table.TableData.Ids;

                sql = "insert into tmp_" + table.Name + " values(?)";
                IDataParameter dp = cmd.CreateParameter();
                dp.DbType = DbType.Int64;
                cmd.Parameters.Add(dp);
                cmd.CommandText = sql;

                for (int i = 0; i < ids.Count(); i++)
                {

                    IDbDataParameter p = cmd.Parameters[0] as IDbDataParameter;
                    p.Value = ids[i];
                    cmd.ExecuteNonQuery();
                }

                sql = "delete from " + table.Name + " where not exists(select 1 from tmp_" + table.Name + " where " + table.Name + ".id=id)";

                cmd.Parameters.Clear();
                cmd.CommandText = sql;
                int r = cmd.ExecuteNonQuery();

                return r;
            }
            catch (Exception er)
            {
                throw er;
            }


        }
    }
}
